﻿CREATE PROCEDURE [maint].[GetDefragScheduleStatus]
@DbName [sysname]=NULL, @CheckDate DATETIME=NULL, @DefragRequired BIT OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@CheckDate = N''' + ISNULL(CAST(@CheckDate AS nvarchar(10)),N'NULL') + ''''
		END
		
	IF(@CheckDate IS NULL)
		SET @CheckDate = GETDATE();

	SELECT
		@DefragRequired =
			MAX(CASE
				WHEN ([DefragWeekSchedule] & POWER(2, DATEPART(dw, @CheckDate -1))) = 0 THEN 0
				WHEN ([DefragDaySchedule] & POWER(2, DATEPART(hh, @CheckDate))) = 0 THEN 0
				ELSE 1
			END)
	FROM [config].[DbMaintenance]
	WHERE ([DbName] = ISNULL(@DbName,[DbName]))
	
	IF(@DefragRequired IS NULL)
	BEGIN
		IF(@DbName IS NULL)
			RAISERROR('There are no databases configured for maintenance.',16,1)
		ELSE
			RAISERROR('Database [%s] does not have any configuration data.',16,1,@DbName)
			
		SET @RC = @@ERROR;
	END

EXIT_PROC:
	RETURN(@RC)
END